package com.piece.module.spider.executor.shape;

import com.piece.core.db.util.DataBaseUtil;
import com.piece.core.framework.util.string.StringUtil;
import com.piece.core.framework.util.net.HtmlUtil;
import com.piece.module.spider.context.SpiderContext;
import com.piece.module.spider.executor.Grammerable;
import com.piece.module.spider.support.Grammer;
import com.piece.module.spider.support.SpiderNode;
import com.piece.module.spider.util.ExpressionUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.springframework.jdbc.core.ArgumentPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import java.lang.reflect.Array;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.*;

/**
 * SQL执行器
 */
@Slf4j
@Component
public class ExecuteSQLExecutor implements ShapeExecutor, Grammerable {

	public static final String DATASOURCE_ID = "datasourceId";

	public static final String SQL = "sql";

	public static final String STATEMENT_TYPE = "statementType";

	public static final String STATEMENT_SELECT = "select";

	public static final String STATEMENT_SELECT_ONE = "selectOne";

	public static final String STATEMENT_SELECT_INT = "selectInt";

	public static final String STATEMENT_INSERT = "insert";

	public static final String STATEMENT_UPDATE = "update";

	public static final String STATEMENT_DELETE = "delete";

	public static final String SELECT_RESULT_STREAM = "isStream";

	public static final String STATEMENT_INSERT_PK = "insertofPk";

	@Override
	public void execute(SpiderNode node, SpiderContext context, Map<String, Object> variables) {
		String dsId = node.getStringJsonValue(DATASOURCE_ID);
		String sql = node.getStringJsonValue(SQL);
		if (StringUtil.isNotEmpty(sql)) {
			// 切换数据源
			if (StringUtil.isNotEmpty(dsId)) {
				DataBaseUtil.changeDataSource(dsId);
			}
			JdbcTemplate template = DataBaseUtil.jdbcTemplate();
			// 把变量替换成占位符
			List<String> parameters = HtmlUtil.getMatchers(sql, "#(.*?)#", true);
			sql = sql.replaceAll("#(.*?)#", "?");
			try {
				Object sqlObject = ExpressionUtil.execute(sql, variables);
				if (null == sqlObject) {
					log.warn("获取的sql为空！");
					return;
				}
				sql = sqlObject.toString();
				context.pause(node.getNodeId(),"common", SQL, sql);
			} catch (Exception e) {
				log.error("获取sql出错,异常信息:{}", e.getMessage(), e);
				ExceptionUtils.wrapAndThrow(e);
			}
			int size = parameters.size();
			Object[] params = new Object[size];
			boolean hasList = false;
			int parameterSize = 0;
			// 当参数中存在List或者数组时，认为是批量操作
			for (int i = 0; i < size; i++) {
				Object parameter = ExpressionUtil.execute(parameters.get(i), variables);
				if (null != parameter) {
					if (parameter instanceof List) {
						hasList = true;
						parameterSize = Math.max(parameterSize, ((List<?>) parameter).size());
					} else if (parameter.getClass().isArray()) {
						hasList = true;
						parameterSize = Math.max(parameterSize, Array.getLength(parameter));
					}
				}
				params[i] = parameter;
			}
			String statementType = node.getStringJsonValue(STATEMENT_TYPE);

			switch (statementType) {
				case STATEMENT_SELECT:
					boolean isStream = "1".equals(node.getStringJsonValue(SELECT_RESULT_STREAM));
					try {
						if (isStream) {
							variables.put("rs", template.queryForRowSet(sql, params));
						} else {
							variables.put("rs", template.queryForList(sql, params));
						}
					} catch (Exception e) {
						variables.put("rs", null);
						log.error("执行sql出错,异常信息:{}", e.getMessage(), e);
						ExceptionUtils.wrapAndThrow(e);
					}
					break;
				case STATEMENT_SELECT_ONE:
					Map<String, Object> rs_one;
					try {
						rs_one = template.queryForMap(sql, params);
						variables.put("rs", rs_one);
					} catch (Exception e) {
						variables.put("rs", null);
						log.error("执行sql出错,异常信息:{}", e.getMessage(), e);
						ExceptionUtils.wrapAndThrow(e);
					}
					break;
				case STATEMENT_SELECT_INT:
					Integer rs_int;
					try {
						rs_int = template.queryForObject(sql, Integer.class, params);
						rs_int = null == rs_int ? 0 : rs_int;
						variables.put("rs", rs_int);
					} catch (Exception e) {
						variables.put("rs", 0);
						log.error("执行sql出错,异常信息:{}", e.getMessage(), e);
						ExceptionUtils.wrapAndThrow(e);
					}
					break;
				case STATEMENT_INSERT:
				case STATEMENT_UPDATE:
				case STATEMENT_DELETE:
					try {
						int updateCount = 0;
						if (hasList) {
							int[] rs = template.batchUpdate(sql, convertParameters(params, parameterSize));
							if (rs.length > 0) {
								updateCount = Arrays.stream(rs).sum();
							}
						} else {
							updateCount = template.update(sql, params);
						}
						variables.put("rs", updateCount);
					} catch (Exception e) {
						log.error("执行sql出错,异常信息:{}", e.getMessage(), e);
						variables.put("rs", -1);
						ExceptionUtils.wrapAndThrow(e);
					}
					break;
				case STATEMENT_INSERT_PK:
					try {
						KeyHolder keyHolder = new GeneratedKeyHolder();
						final String insertSQL = sql;
						template.update(con -> {
							PreparedStatement ps = con.prepareStatement(insertSQL, Statement.RETURN_GENERATED_KEYS);
							new ArgumentPreparedStatementSetter(params).setValues(ps);
							return ps;
						}, keyHolder);
						variables.put("rs", keyHolder.getKey().intValue());
					} catch (Exception e) {
						log.error("执行sql出错,异常信息:{}", e.getMessage(), e);
						variables.put("rs", -1);
						ExceptionUtils.wrapAndThrow(e);
					}
					break;
			}
		}
	}

	private List<Object[]> convertParameters(Object[] params, int length) {
		List<Object[]> result = new ArrayList<>(length);
		int size = params.length;
		for (int i = 0; i < length; i++) {
			Object[] parameters = new Object[size];
			for (int j = 0; j < size; j++) {
				parameters[j] = getValue(params[j], i);
			}
			result.add(parameters);
		}
		return result;
	}

	private Object getValue(Object object, int index) {
		if (null == object) {
			return null;
		} else if (object instanceof List) {
			List<?> list = (List<?>) object;
			int size = list.size();
			if (size > 0) {
				return list.get(Math.min(list.size() - 1, index));
			}
		} else if (object.getClass().isArray()) {
			int size = Array.getLength(object);
			if (size > 0) {
				Array.get(object, Math.min(-1, index));
			}
		} else {
			return object;
		}
		return null;
	}

	@Override
	public String supportShape() {
		return "executeSql";
	}

	@Override
	public List<Grammer> grammers() {
		Grammer grammer = new Grammer();
		grammer.setComment("执行SQL结果");
		grammer.setFunction("rs");
		grammer.setReturns(Arrays.asList("List<Map<String,Object>>", "int"));
		return Collections.singletonList(grammer);
	}
}
